/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package server.DAO.MySQLDAO;

import common.DAO.ProductDAO;
import common.Objects.Component;
import common.Objects.Product;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
/**
 *
 * @author Slasher
 */
public class MYSQLProductDAO implements ProductDAO {

    public Product getProduct(int id) {
        Product product = new Product();
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statement = c.createStatement();
                ResultSet rs = statement.executeQuery(
                        "SELECT * FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCTS WHERE Id='" + id + "';");

                if (rs.isBeforeFirst()){
                    rs.next();
                }
                if (rs.first()){
                        product.setId(rs.getInt("Id"));
                        product.setName(rs.getString("Name"));
                        product.setDescription(rs.getString("Description"));
                        product.setPrice(rs.getDouble("Price"));
                }
                statement = c.createStatement();
                c.close();
            }
        } catch(Exception e){e.printStackTrace();}

        Vector<Component> tempComps = getProductsComponents(product.getId());
        if (tempComps.isEmpty() == false){
            product.setComponents(tempComps);
        }        
        return product;
    }
    public Vector<Product> getProducts() {
        Vector<Product> products = new Vector<Product>();
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statement = c.createStatement();
                ResultSet rs = statement.executeQuery(
                        "SELECT * FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCTS;");

                if (rs.isBeforeFirst()){
                    rs.next();
                }
                if (rs.first()){
                    do {
                        Product tempProduct = new Product();

                        tempProduct.setId(rs.getInt("Id"));
                        tempProduct.setName(rs.getString("Name"));
                        tempProduct.setDescription(rs.getString("Description"));
                        tempProduct.setPrice(rs.getDouble("Price"));

                        products.add(tempProduct);
                    }
                    while (rs.next());
                }
                c.close();
            }
        } catch(Exception e){e.printStackTrace();}
        for (Product product : products) {
            Vector<Component> tempComps = getProductsComponents(product.getId());
            if (tempComps.isEmpty() == false){
                product.setComponents(tempComps);
            }
        }
        return products;
    }
    private Vector<Component> getProductsComponents(int id) {
        Vector<Component> components = new Vector<Component>();
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statement = c.createStatement();
                ResultSet rs = statement.executeQuery(
                        "SELECT * FROM " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS" +
                        " WHERE Id IN (SELECT Comp_Id FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCT_LOOKUP" +
                        " WHERE Prod_Id = " + id + ");");
                if (rs.isBeforeFirst()){
                    rs.next();
                }
                if (rs.first()){
                    do {
                        Component tempComponent = new Component();

                        tempComponent.setId(rs.getInt("Id"));
                        tempComponent.setName(rs.getString("Name"));
                        tempComponent.setDescription(rs.getString("Description"));
                        tempComponent.setCurrentStock(rs.getInt("Current_Stock"));
                        tempComponent.setPrice(rs.getDouble("Price"));
                        tempComponent.setCategoryId(rs.getInt("Category_Id"));

                        components.add(tempComponent);
                    }
                    while (rs.next());
                }
                c.close();
            }
        } catch(Exception e){e.printStackTrace();}
        return components;
    }
    public boolean createProduct(Product product) {
        boolean status = false;
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statment = c.createStatement();
                if ( statment.executeUpdate(
                    "INSERT INTO " + MySQLDAOFactory.DBNAME + "." + "PRODUCTS ( "
                    + "Id, "
                    + "Name, "
                    + "Description, "
                    + "Price "
                    + " ) VALUES ( "
                    + "NULL, "
                    + "'" + product.getName() + "', "
                    + "'" + product.getDescription() + "', "
                    + "'" + product.getPrice() + "' "
                    + ");", statment.RETURN_GENERATED_KEYS) > 0 ) {
                    status = true;
                }
                ResultSet keys = statment.getGeneratedKeys();
                int lastKey = 1;
                while (keys.next()) {
                  lastKey = keys.getInt(1);
                }
                product.setId(lastKey);
                c.close();
            }
            else {
                status = false;
            }
        } catch (Exception e){
            e.printStackTrace();
            status = false;
        }
        if (status) {
            status = insertComponents(product);
        }
        return status;
    }
    public boolean editProduct(Product product) {
        if ( MySQLDAOFactory.dbUpdate(
                "UPDATE " + MySQLDAOFactory.DBNAME + "." + "PRODUCTS "
                + "SET "
                + "Name = '"+ product.getName() +"', "
                + "Description = '"+ product.getDescription() +"', "
                + "Price = '"+ product.getPrice() +"' "
                + "WHERE Id = '" + product.getId() + "'"
                + ";") ) {
            if (MySQLDAOFactory.dbUpdate(
            "DELETE FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCT_LOOKUP WHERE Prod_Id = "
            +product.getId()+";")) {
                    return insertComponents(product);
            }
        }
        return false;
        
    }
    private boolean insertComponents (Product product) {
        if (product.getComponents().size() > 0) {
            String sqlString = "INSERT INTO " + MySQLDAOFactory.DBNAME + "." + "PRODUCT_LOOKUP ( "
                    + "Prod_Id, "
                    + "Comp_Id ) VALUES ";

            for (int i = 0; i < product.getComponents().size();i++) {
                sqlString += "('" + product.getId() + "', "
                    + "'" + product.getComponents().get(i).getId() + "' "
                    + ")";
                if (i == product.getComponents().size() - 1) {
                    sqlString += ";";
                }
                else {
                    sqlString += ",";
                }
            }
            return MySQLDAOFactory.dbUpdate(sqlString);
        } else {
            return false;
        }
    }
    public boolean deleteProduct(int id) {
        MySQLDAOFactory.dbUpdate(
                "DELETE FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCT_LOOKUP WHERE Prod_Id = "
                +id+";");
        return MySQLDAOFactory.dbUpdate(
                "DELETE FROM " + MySQLDAOFactory.DBNAME + "." + "PRODUCTS WHERE Id = "
                +id+";");
    }
}
